/**
 * Created by jackliu on 15/12/16.
 */

var common = require('../../common');
var pool = require('../index');
var mysql = require('mysql');
var ItbBaseModel = require('./ItbBaseModel');
var CONFIG = require('../../config');


// Schema 结构
var ItbTradeOrderModel = function(obj){
    ItbBaseModel.call(this,obj);

  // 以下为表定义数据，需要的字段就填入
  //`order_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '订单id',
  //`order_no` varchar(50) NOT NULL DEFAULT '' COMMENT 'itb自定义订单号',
  //`detail_cnt` int(8) NOT NULL DEFAULT '0' COMMENT '子订单数',
  //`state` int(3) NOT NULL DEFAULT '0' COMMENT '订单状态:10待支付、11已支付、12支付失败、20制作中、30配送中、40完成、50取消',
  //`inv_id` bigint(20) DEFAULT NULL COMMENT '发票id，为null表示未开票',
  //`partner_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '合作平台，如紫云',
  //`pay_platform` int(3) NOT NULL DEFAULT '0' COMMENT '支付平台: 10微信, 20支付宝',
  //`total_fee` bigint(12) NOT NULL DEFAULT '0' COMMENT '订单总金额（单位为rmb分）',
  //`coupon_fee` bigint(12) NOT NULL DEFAULT '0' COMMENT '子项：优惠费（单位为rmb分）',
  //`coupon_id` bigint(20) unsigned DEFAULT NULL COMMENT '优惠券id',
  //`addr_id` bigint(20) DEFAULT NULL COMMENT '收件地址id',
  //`user_id` bigint(20) DEFAULT NULL COMMENT '用户id',
  //`type` int(3) NOT NULL DEFAULT '0' COMMENT '订单类型，预留',
  //`desc` varchar(200) DEFAULT NULL COMMENT '订单描述',
  //`create_time` datetime NOT NULL,
  //`update_time` datetime NOT NULL,
  //`update_by` varchar(50) NOT NULL,
  //`del_flg` int(1) NOT NULL DEFAULT '0',


};
ItbTradeOrderModel.prototype = new ItbBaseModel();

// 取得表名
ItbTradeOrderModel.prototype.getTableName = function(){
    return 'itb_trade_order';
}
// 取得查询语句中where 后面匹配主键的条件（需要各派生类进行重载）
ItbTradeOrderModel.prototype.getWhereConditionOfKeyEqual = function(){
    return 'order_id='+this.order_id;
}

// 插入
ItbTradeOrderModel.prototype.insToDB = function(callback){
    var THIS = this;
    var tableName = this.getTableName();
    pool.pool.getConnection(function(err, connection) {
        // Use the connection
        var sql = 'INSERT INTO '+tableName+' SET ? ,update_time=sysdate() ';
        var sqlValue = [ THIS ];
        var sql = mysql.format(sql, sqlValue);
        if(!THIS.create_time) {
            sql += ',create_time=sysdate()';
        }
        console.log(sql);
        connection.query(sql, function(err, result) {
            // And done with the connection.
            connection.release();

            if (err)
                console.log(err);

            callback(err, result.insertId);
        });
    });
};

//-----------------------------------------------------------
/**
 * 根据用户 id, 订单状态, 获取用户订单信息
 * @param bForCnt: true [仅用于取count]; false [取数据]
 * @param userId: 用户唯一标识
 * @param states: 订单状态，10待支付、11已支付、12支付失败、20制作中、30配送中、40完成、50取消'。所需订单状态数组, 如[10, 30]，如果不传则返回全部状态订单
 * @param pageNum: 请求页码
 * @param pageSize: 请求页大喜
 * @return {string}
 * add: 2017-03-10 by: wangJun
 * */
// todo 新增方法, 待审核, begin
function sql_GetPrintOrderIdsOfStates(bForCnt, userId, states, pageNum, pageSize){

    var sql = '';
    if(bForCnt) {
        sql += ' SELECT COUNT(DISTINCT o.order_id) AS count ';
    } else {
        sql += ' SELECT DISTINCT o.order_id ';
    }
    sql +=
        " FROM itb_trade_order o, " +
        "      itb_trade_order_detail d " +
        " WHERE o.del_flg=0 " +
        "   AND o.hide=0 " +
        "   AND o.type=0 " +
        "   AND o.user_id= " + userId +
        "   AND d.order_id = o.order_id " +
        "   AND d.del_flg=0 ";
    // 如果指定了分类
    if(states && states.length > 0) {
        sql += ' AND o.state IN (' + states.join(',') + ') ';
    }
    sql += ' ORDER BY o.create_time DESC ';

    // 分页
    var skipCnt = Number(pageNum||0)*Number(pageSize||0);
    if(Number(pageSize||0)>0){
        sql += " LIMIT " + skipCnt + "," + Number(pageSize);
    }


    return sql;
}
// todo 新增方法, 待审核, end
//-----------------------------------------------------------
/**
 * 获取满足要求的订单id列表
 *
 * 参数: userId	用户唯一标识
 * 参数: states	订单状态，10待支付、11已支付、12支付失败、20制作中、30配送中、40完成、50取消'。所需订单状态数组, 如[10, 30]，如果不传则返回全部状态订单
 * 参数: pageNum	请求页
 * 参数: pageSize	请求页大小
 * 参数：callback：回调，参数如下：
 *          error:错误消息
 *          ids: 订单id列表
 * modify: 2017-03-10 by: wangJun
 * */
// todo 修改方法, 待审核, begin
ItbTradeOrderModel.prototype.getPrintOrderIdsOfStates = function(userId, states,pageNum,pageSize, callback){
    var THIS = this;

    pool.pool.getConnection(function(err, connection) {
        if (err) {
            console.log('ItbTradeOrderModel.getPrintOrderIdsOfStates Err:'+err);
            return callback(err, null);
        }
        var sql = sql_GetPrintOrderIdsOfStates(false, userId, states, pageNum, pageSize);

        console.log(sql);
        connection.query(sql, function (error, results) {
            connection.release();
            console.log();
            if(error){
                console.log(error);
                return callback(error,null);
            }
            if(!results || results.length==0){
                return callback(null, {});
            }

            var ids = [];
            for(var i=0;i<results.length;i++){
                ids.push(results[i].order_id);
            }
            return callback(null, ids);
        });
    });
}
// todo 修改方法, 待审核, begin
//----------------------------------------------------------------
/**
 * 获取满足要求的订单id列表的个数
 *
 * 参数: userId	用户唯一标识
 * 参数: states	订单状态，10待支付、11已支付、12支付失败、20制作中、30配送中、40完成、50取消'。所需订单状态数组, 如[10, 30]，如果不传则返回全部状态订单
 * 参数：callback：回调，参数如下：
 *          error:错误消息
 *          count: 满足要求的订单 id 列表的个数
 * add: 2017-03-10 by: wangJun
 * */
// todo 新增方法, 待审核, begin
ItbTradeOrderModel.prototype.getPrintOrderIdsOfStatesCnt = function (userId, states, callback) {
    pool.pool.getConnection(function(error, connection){

        if(error) {
            console.log("ItbTradeOrderModel.getPrintOrderIdsOfStatesCnt err: " + error);
            return callback(error, null);
        }

        var sql = sql_GetPrintOrderIdsOfStates(true, userId, states);
        console.log(sql);
        connection.query(sql, function(error, results){
            connection.release();

            // 回调
            if(results && results.length>0){
                callback(error, results[0].count);
            }else{
                callback(error, 0);
            }
        });
    });

}
// todo 新增方法, 待审核, begin

//------------------------------------------------------------------
/* 获取订单快递公司信息
 * 参数: orerId	订单id
 * 参数：callback：回调，参数如下：
 *          error:错误消息
 *          data: 获取订单快递公司信息
 *              [{
 * 	                "detailOrderId":1,
 * 	                "name":"中通快递",
 * 	                "no":9876543212345
 * 	            }]
 */
ItbTradeOrderModel.prototype.getDeliveryInfo = function(orderId, callback){
    var THIS = this;
    var tableName = this.getTableName();

    pool.pool.getConnection(function(err, connection) {
        if (err) {
            console.log('ItbTradeOrderModel.getDeliveryInfo Err:'+err);
            return callback(err, null);
        }
        // Use the connection
        var sql =
            " select  d.detail_id as detailOrderId, "+
            " 		d.express as `name`, "+
            " 		d.tracking_no as `no` "+
            " 		, d.exc_id as excId "+ // add by wangjun 2017-10-25 增加物流公司 id 返回
            " from itb_trade_order_detail d "+
            " where d.del_flg=0 "+
            " and d.order_id=" + orderId;

        //var sqlValue = [];
        //var sql = mysql.format(sql, sqlValue);
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();

            console.log();

            if(error){
                return callback(error, null);
            }
            if(!result || result.length==0){
                return callback(null,null);
            }

            return callback(null, result);
        });
    });
}

//------------------------------------------------------------------
/* 获取未开票已支付订单总金额列表
 * 参数: ids	发票对应商品订单数组 [123, 456,789]
 * 参数：callback：回调，参数如下：
 *          error:错误消息
 *          data: 订单的总金额数组，如下：
 *                  [{
 *                      order_id:1,
 *                      total_fee:100,
 *                      state:12
 *                  }]
 */
ItbTradeOrderModel.prototype.getFeeOfNoInvoicePayedOrder = function(ids, callback){
    var THIS = this;
    var tableName = this.getTableName();

    pool.pool.getConnection(function(err, connection) {
        if (err) {
            console.log('ItbTradeOrderModel.getFeeOfNoInvoicePayedOrder Err:'+err);
            return callback(err, null);
        }
        // Use the connection
        var sql =
            " select o.order_id, o.total_fee, o.state  "+
            " from itb_trade_order o "+
            " where o.order_id in( "+ids.join(",")+")"+
            " and o.del_flg=0 "+
            " and o.inv_id is null "+
            " and o.state in(11,20,30,40) ";

        //var sqlValue = [];
        //var sql = mysql.format(sql, sqlValue);
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();

            console.log();

            if(error){
                return callback(error, null);
            }
            if(!result || result.length==0){
                return callback(null,null);
            }

            return callback(null, result);
        });
    });
}

//------------------------------------------------------------------
/* 获取可开发票的订单
 * 参数: userId
 * 参数: callback:回调
 */
ItbTradeOrderModel.prototype.getInvoiceAbleOrderByUser = function(userId, callback){
    var THIS = this;
    var tableName = this.getTableName();

    pool.pool.getConnection(function(err, connection) {
        if (err) {
            console.log('ItbTradeOrderModel.getInvoiceAbleOrderByUser Err:'+err);
            return callback(err,null);
        }
        // Use the connection
        var sql =
            " select o.* "+
            " from itb_trade_order o "+
            " where o.user_id = "+userId+
            " and o.del_flg=0 "+
            " and o.type=0 "+// added 2017-06-15
            " and (o.inv_id is null OR o.inv_id=0) "+
            " and o.state in(11,20,30,40) "+
            " order by o.update_time DESC ";

        //var sqlValue = [];
        //var sql = mysql.format(sql, sqlValue);
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();

            console.log();

            if(result && result.length>0) {
                return callback(null, result);
            } else {
                return callback(error, null);
            }
        });
    });
}


//------------------------------------------------------------------
/* 确认完成订单
 * 参数: userId
 * 参数: callback:回调
 */
ItbTradeOrderModel.prototype.safeConfirmOrder = function(userId, orderId, callback){
    var THIS = this;
    var tableName = this.getTableName();

    pool.pool.getConnection(function(err, connection) {
        if (err) {
            console.log('ItbTradeOrderModel.safeConfirmOrder Err:'+err);
            return callback(err, null);
        }
        // Use the connection
        var sql = ' UPDATE itb_trade_order SET state=40, finish_time=sysdate(), update_time=sysdate(), update_by=' +userId+
            ' WHERE user_id=' +userId+' AND order_id='+orderId+' AND del_flg=0 ';

        //var sqlValue = [];
        //var sql = mysql.format(sql, sqlValue);
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();

            console.log();

            if(error){
                return callback(error);
            }
            if(!result){
                return callback(null);
            }
            if(result.affectedRows==0){
                return callback("确认订单失败");
            }
            return callback(null);
        });
    });
}


//------------------------------------------------------------------
/* 获取订单各相关fee信息
 * 参数: orderId
 * 参数: callback:回调
 */
ItbTradeOrderModel.prototype.getOrderFeeInfo = function(orderId, callback){
    var THIS = this;
    var tableName = this.getTableName();

    pool.pool.getConnection(function(err, connection) {
        if (err) {
            console.log('ItbTradeOrderModel.getOrderFeeInfo Err:'+err);
            return callback(err, null);
        }
        // Use the connection
        var sql = ' select t.*, sum(d.fee) AS sum_fee, sum(d.product_fee) as sum_product_fee, '+
            ' sum(d.design_fee) as sum_design_fee, sum(d.mail_fee) as sum_mail_fee '+
            ' from itb_trade_order t, itb_trade_order_detail d '+
            ' where t.order_id='+orderId+' and t.del_flg=0 '+
            ' and t.order_id=d.order_id and d.del_flg=0 ';

        //var sqlValue = [];
        //var sql = mysql.format(sql, sqlValue);
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();

            console.log();

            return callback(error, result);
        });
    });
}


//------------------------------------------------------------------
/* 获取地址详情
 * 参数: userId
 * 参数: callback:回调，参数如下：
 *          error:
 *          address: ItbAddressModel
 */
ItbTradeOrderModel.prototype.findAddressFromDB = function( callback){
    var THIS = this;
    var tableName = this.getTableName();

    pool.pool.getConnection(function(err, connection) {
        if (err) {
            console.log('ItbTradeOrderModel.findAddressFromDB Err:'+err);
            return callback(err, null);
        }
        // Use the connection
        var sql = " select a.*  "+
            " from itb_trade_order t, "+
            " 	itb_address a "+
            " where t.order_id= "+ THIS.order_id+
            " and t.del_flg=0 "+
            " and t.addr_id=a.addr_id "+
            " and a.del_flg=0 ";

        //var sqlValue = [];
        //var sql = mysql.format(sql, sqlValue);
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();

            console.log();

            if(error){
                return callback(error,null);
            }
            if(!result || result.length==0){
                return callback(null,null);
            }

            return callback(null,result[0]);
        });
    });
}

//------------------------------------------------------------------
/* 获取可关闭的订单
 * 参数: callback:回调
 */
ItbTradeOrderModel.prototype.getAbleCloseOrder = function(skipCnt, pageSize, callback){
    var THIS = this;
    var tableName = this.getTableName();

    pool.pool.getConnection(function(err, connection) {
        if (err) {
            console.log('ItbTradeOrderModel.getAbleCloseOrder Err:'+err);
            return callback(err,null);
        }
        // Use the connection
        var MIN_LIMIT = Number(CONFIG.TRADE_ORDER_AUTO_CLOSE_HOUR_LIMIT || 24) * 60;
        var sql = " SELECT * FROM ( "+
            " select t.* "+
            " from itb_trade_order t "+
            " where t.del_flg=0 "+
            " and (t.inv_id is null OR t.inv_id=0) "+
            " and t.state IN (10, 12) "+
            " and t.create_time < date_sub(now(), INTERVAL "+MIN_LIMIT+" MINUTE) "+
            " order by t.create_time, order_id ) tt ";

        if(typeof skipCnt!='undefined' && skipCnt != null && typeof pageSize!='undefined' && pageSize != null){
            sql += " limit " + Number(skipCnt) + "," + Number(pageSize);
        }

        //var sqlValue = [];
        //var sql = mysql.format(sql, sqlValue);
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();

            console.log();

            if(result && result.length>0) {
                return callback(null, result);
            } else {
                return callback(error, null);
            }
        });
    });
};
ItbTradeOrderModel.prototype.getAbleCloseOrderCnt = function(callback){
    var THIS = this;
    var tableName = this.getTableName();

    pool.pool.getConnection(function(err, connection) {
        if (err) {
            console.log('ItbTradeOrderModel.getAbleCloseOrderCnt Err:'+err);
            return callback(err,null);
        }
        // Use the connection
        var MIN_LIMIT = Number(CONFIG.TRADE_ORDER_AUTO_CLOSE_HOUR_LIMIT || 24) * 60;
        var sql =
            " select COUNT(*) AS cnt "+
            " from itb_trade_order t "+
            " where t.del_flg=0 "+
            " and (t.inv_id is null OR t.inv_id=0) "+
            " and t.state IN (10, 12) "+
            " and t.create_time < date_sub(now(), INTERVAL "+MIN_LIMIT+" MINUTE) ";

        //var sqlValue = [];
        //var sql = mysql.format(sql, sqlValue);
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();

            if(result && result.length>0) {
                return callback(null, result[0].cnt||0);
            } else {
                return callback(error, 0);
            }
        });
    });
};
//------------------------------------------------------------------
/** 关闭超时订单
 */
ItbTradeOrderModel.prototype.cancelOvertimeOrder = function(orderId,userId, callback){
    var THIS = this;
    var tableName = this.getTableName();

    pool.pool.getConnection(function(err, connection) {
        if (err) {
            console.log('ItbTradeOrderModel.cancelOvertimeOrder Err:'+err);
            return callback(err,null);
        }
        // Use the connection
        var MIN_LIMIT = Number(CONFIG.TRADE_ORDER_AUTO_CLOSE_HOUR_LIMIT || 24) * 60;
        var sql =
            " update itb_trade_order t "+
            " set t.state=50, t.update_time=sysdate(), t.update_by= "+userId+
            " where t.del_flg=0 "+
            " and (t.inv_id is null OR t.inv_id=0) "+
            " and t.state IN (10, 12) "+
            " and t.order_id="+orderId+
            " and t.create_time < date_sub(now(), INTERVAL "+MIN_LIMIT+" MINUTE) ";

        //var sqlValue = [];
        //var sql = mysql.format(sql, sqlValue);
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();

            console.log();

            if(error){
                return callback(error, 0);
            }
            if(result){
                return callback(null, result.affectedRows||0);
            }
            return callback(null, 0);
        });
    });
};

//------------------------------------------------------------------
/* 获取可确认的订单
 * 参数: callback:回调
 */
ItbTradeOrderModel.prototype.getAbleDoneOrder = function(skipCnt, pageSize, callback){
    var THIS = this;
    var tableName = this.getTableName();

    pool.pool.getConnection(function(err, connection) {
        if (err) {
            console.log('ItbTradeOrderModel.getAbleDoneOrder Err:'+err);
            return callback(err,null);
        }
        // Use the connection
        var MIN_LIMIT = Number(CONFIG.TRADE_ORDER_DONE_CLOSE_DAY_LIMIT || 7) * 1440;
        var sql = ' select * from ( '+
            ' SELECT d.* '+
            ' FROM itb_trade_order d, '+
            ' (SELECT h.trade_obj_id AS order_id, MAX(h.create_time) AS his_create_time '+
            '    FROM itb_trade_state_his h '+
            '   WHERE h.del_flg=0 AND h.type=10 and h.state=30 '+// 配送中
            '     AND h.trade_obj_id IN (SELECT t.order_id FROM itb_trade_order t WHERE t.del_flg=0 AND t.state=30) '+
            '   GROUP BY h.trade_obj_id) a '+
            ' WHERE d.order_id=a.order_id AND d.del_flg=0 AND d.state=30 '+
            ' AND a.his_create_time < date_sub(now(), INTERVAL '+MIN_LIMIT+' MINUTE) '+
            ' order by a.his_create_time, d.order_id ) tt ';

        if(typeof skipCnt!='undefined' && skipCnt != null && typeof pageSize!='undefined' && pageSize != null){
            sql += " limit " + Number(skipCnt) + "," + Number(pageSize);
        }

        //var sqlValue = [];
        //var sql = mysql.format(sql, sqlValue);
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();

            console.log();

            if(result && result.length>0) {
                return callback(null, result);
            } else {
                return callback(error, null);
            }
        });
    });
};
ItbTradeOrderModel.prototype.getAbleDoneOrderCnt = function(callback){
    var THIS = this;
    var tableName = this.getTableName();

    pool.pool.getConnection(function(err, connection) {
        if (err) {
            console.log('ItbTradeOrderModel.getAbleDoneOrderCnt Err:'+err);
            return callback(err,null);
        }
        // Use the connection
        var MIN_LIMIT = Number(CONFIG.TRADE_ORDER_DONE_CLOSE_DAY_LIMIT || 7) * 1440;
        var sql =
            ' select COUNT(d.order_id) AS cnt '+
            ' FROM itb_trade_order d, '+
            ' (SELECT h.trade_obj_id AS order_id, MAX(h.create_time) AS his_create_time '+
            '    FROM itb_trade_state_his h '+
            '   WHERE h.del_flg=0 AND h.type=10 and h.state=30 '+// 配送中
            '     AND h.trade_obj_id IN (SELECT t.order_id FROM itb_trade_order t WHERE t.del_flg=0 AND t.state=30) '+
            '   GROUP BY h.trade_obj_id) a '+
            ' WHERE d.order_id=a.order_id AND d.del_flg=0 AND d.state=30 '+
            ' AND a.his_create_time < date_sub(now(), INTERVAL '+MIN_LIMIT+' MINUTE) ';

        //var sqlValue = [];
        //var sql = mysql.format(sql, sqlValue);
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();

            if(result && result.length>0) {
                return callback(null, result[0].cnt||0);
            } else {
                return callback(error, 0);
            }
        });
    });
};
//------------------------------------------------------------------
/* 确认超时订单
 */
ItbTradeOrderModel.prototype.confirmOvertimeOrder = function(orderId, userId, callback){
    var THIS = this;
    var tableName = this.getTableName();

    pool.pool.getConnection(function(err, connection) {
        if (err) {
            console.log('ItbTradeOrderModel.confirmOvertimeOrder Err:'+err);
            return callback(err,null);
        }
        // Use the connection
        var MIN_LIMIT = Number(CONFIG.TRADE_ORDER_DONE_CLOSE_DAY_LIMIT || 7) * 1440;
        var sql =
            ' UPDATE itb_trade_order d, '+
            ' (SELECT h.trade_obj_id AS order_id, MAX(h.create_time) AS his_create_time '+
            '    FROM itb_trade_state_his h '+
            '   WHERE h.del_flg=0 AND h.type=10 and h.state=30 '+// 配送中
            '     AND h.trade_obj_id= '+orderId+
            '   GROUP BY h.trade_obj_id) a '+
            ' SET d.state=40, d.finish_time=sysdate(), d.update_time=sysdate(), d.update_by= '+userId+
            ' WHERE d.order_id=a.order_id AND d.del_flg=0 AND d.state=30 '+
            ' AND a.his_create_time < date_sub(now(), INTERVAL '+MIN_LIMIT+' MINUTE) ';

        //var sqlValue = [];
        //var sql = mysql.format(sql, sqlValue);
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();

            console.log();

            if(error){
                return callback(error, 0);
            }
            if(result){
                return callback(null, result.affectedRows||0);
            }
            return callback(null, 0);
        });
    });
};

//------------------------------------------------------------------
/* 清除关联发票信息
 */
ItbTradeOrderModel.prototype.clrInvInfo = function(invId, userId, callback){
    var THIS = this;
    var tableName = this.getTableName();

    pool.pool.getConnection(function(err, connection) {
        if (err) {
            console.log('ItbTradeOrderModel.clrInvInfo Err:'+err);
            return callback(err,null);
        }
        // Use the connection
        var DAY_LIMIT = Number(CONFIG.TRADE_ORDER_DONE_CLOSE_DAY_LIMIT || 7);
        var sql =
            ' UPDATE itb_trade_order d '+
            ' SET d.inv_id=null, d.update_time=sysdate(), d.update_by= '+userId+
            ' WHERE d.del_flg=0 AND d.inv_id= '+invId;

        //var sqlValue = [];
        //var sql = mysql.format(sql, sqlValue);
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();

            console.log();

            if(error){
                return callback(error, 0);
            }
            if(result){
                return callback(null, result.affectedRows||0);
            }
            return callback(null, 0);
        });
    });
};

//------------------------------------------------------------------
/**
 * 更新订单信息，包括费用、子订单个数等字段
 * @param orderId 订单ID
 * @param updKind 数据变动的类型:
 *                1  印品商品订单相关的修改（套餐、数量）
 *                2  配件商品相关的修改（配件商品、数量）
 *                4  地址修改
 *                8  优惠券修改
 *                支持同时更新多个数据,例如:
 *                15 表示所有数据发生了修改
 *                3  表示印品商品和配件商品发生了修改
 * @param callback
 */
ItbTradeOrderModel.prototype.procUpdOrderInfo = function(orderId, updKind , callback){
    var THIS = this;
    var tableName = this.getTableName();

    pool.pool.getConnection(function(err, connection) {
        // Use the connection
        // 参数：［OUT］f_result 总费用
        var sql = 'CALL proc_trade_order_info_update('+orderId+','+updKind+',@f_result);';

        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();

            console.log("[proc_trade_order_info_update] Result is:\n"+result);

            callback(error, null);
        });
    });
};


/**
 * 根据地址 id 反查订单 id
 * @param addressId 地址 id
 * @param callback
 *          error 错误
 *          orderInfo 订单记录
 * */
// todo 新增接口 待审核 begin
ItbTradeOrderModel.prototype.getOrderInfByAddressId = function(addressId, callback){

    pool.pool.getConnection(function(err, connection) {
        // Use the connection
        var sql = " SELECT * " +
            " FROM itb_trade_order t " +
            " WHERE t.addr_id= " + addressId +
            "   AND t.del_flg=0 " ;
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();

            console.log("[proc_trade_order_info_update] Result is:\n"+result);

            if(error) {
                return callback(error, null);
            }

            if(result && result.length == 1) {
                return callback(error, result[0]);
            } else {
                return callback(null, null);
            }
        });
    });

};
// todo 新增接口 待审核 end

//------------------------------------------------------------------
/* 获取微支付的vip订单
 * 参数: itemId[可选]
 */
ItbTradeOrderModel.prototype.getUnpaidVipOrder = function(userId, itemId, callback){
    var THIS = this;
    var tableName = this.getTableName();

    pool.pool.getConnection(function(err, connection) {
        if (err) {
            console.log('ItbTradeOrderModel.getUnpaidVipOrder Err:'+err);
            return callback(err,null);
        }
        // Use the connection
        var sql = ' SELECT t.* '+
            ' FROM itb_trade_order t '+
            ' WHERE t.del_flg=0 AND t.state in (10,12) '+
            ' AND t.user_id = '+userId+
            ' AND t.order_id IN '+
            ' ( SELECT d.order_id '+
            ' FROM itb_trade_order_detail d, itb_items i, tg_type p '+
            ' WHERE d.del_flg=0 '+
            ' AND i.del_flg=0 AND d.item_id = i.item_id '+
            ' AND p.del_flg=0 AND p.type_id = i.type_id AND p.top_parent=9 ';// 虚拟商品
        if(itemId>0) {
            sql += ' AND d.item_id='+itemId;
        }
        sql += ') ORDER BY t.create_time DESC ';

        //var sqlValue = [];
        //var sql = mysql.format(sql, sqlValue);
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();

            console.log();

            if(result && result.length>0) {
                return callback(null, result);
            } else {
                return callback(error, null);
            }
        });
    });
};

//------------------------------------------------------------------
// 获取用户秒杀列表
// state: 10, 可用, 20 已用, 30 失效, 40 待支付
ItbTradeOrderModel.prototype.getUserSeckillActivityList= function(userId, state, pageNum, pageSize,callback){
    var THIS = this;
    var tableName = this.getTableName();

    if(state != 10 && state != 20 && state !=30 && state !=40) {
        return callback('参数错误',null);
    }

    pool.pool.getConnection(function(err, connection) {
        if (err) {
            console.log('ItbTradeOrderModel.getUserSeckillActivityList Err:'+err);
            return callback(err,null);
        }
        // Use the connection

        // 所有秒杀订单
        var sql1 = ' SELECT od.*, dtl.detail_id, dtl.item_id AS act_r_id '+
            ' FROM itb_trade_order od '+
            ' INNER JOIN itb_trade_order_detail dtl '+// 子订单只可能有一个
            ' ON dtl.del_flg=0 AND dtl.order_id=od.order_id '+
            ' WHERE od.del_flg=0 AND od.type=20 '+// 秒杀原始订单
            ' AND od.user_id='+userId;
        if(state==40) {
            sql1 += ' AND od.state IN (10, 12) '+// 支付失败
                ' AND (UNIX_TIMESTAMP(now())-UNIX_TIMESTAMP(od.create_time))<=300 '+// 过滤超时的订单
                ' GROUP BY od.order_id ';
        } else {
            sql1 += ' AND od.state IN (11, 20, 30, 40) '+// 支付成功
                ' GROUP BY od.order_id ';
        }

        // 主sql
        var sql = ' SELECT '+state+' AS state, '+
            ' x.order_id AS orderId, x.order_no AS orderNo, '+
            ' ar.item_id AS itemId, it.item_name AS title, '+
            ' CASE WHEN it.theme_thumb IS NULL OR it.theme_thumb="" '+
            '      THEN func_parse_string_array(it.thumbs,0) '+
            '      ELSE it.theme_thumb '+
            ' END AS thumb, '+
            ' t.top_parent AS typeL, t.parent AS typeM, '+
            ' DATE_FORMAT(ar.expiry_time, "%Y-%m-%d %H:%i") AS expiryTime, '+
            ' DATE_FORMAT(x.create_time, "%Y-%m-%d %H:%i") AS createTime, '+
            ' UNIX_TIMESTAMP(now()) AS currentTime, '+
            ' UNIX_TIMESTAMP(x.create_time)+300 AS cancelTime '+
            ' FROM ';

        sql += ' ( '+sql1+' ) x ';

        sql += ' INNER JOIN itb_activity_r ar '+
            ' ON ar.del_flg=0 AND ar.r_id=x.act_r_id ';
        if(state==10) {
            sql += ' AND ar.expiry_time > sysdate() ';// 有效期内
        } else if(state==30) {
            sql += ' AND ar.expiry_time <= sysdate() ';// 过期
        }

        sql += ' INNER JOIN itb_items it '+
            ' ON it.del_flg=0 AND it.item_id=ar.item_id ';

        sql += ' INNER JOIN tg_type t '+
            ' ON t.del_flg=0 AND t.type_id=it.type_id ';

        if(state!=40) {
            sql += ' INNER JOIN itb_trade_order y '+// 商品订单
                ' ON y.del_flg=0 AND y.type=0 AND y.order_no!=y.pay_order_no AND y.pay_order_no=x.order_no ';
            if(state==10 || state==30) {
                sql += ' AND y.state IN (11) ';
            } else if(state==20) {
                sql += ' AND y.state NOT IN (11) ';
            }
        }

        sql += ' ORDER BY x.create_time DESC ';

        var skipCnt = Number(pageNum||0)*Number(pageSize||0);
        if(Number(pageSize||0)>0){
            sql += " limit " + skipCnt + "," + Number(pageSize);
        }

        //var sqlValue = [];
        //var sql = mysql.format(sql, sqlValue);
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();

            console.log();

            if(result && result.length>0) {
                return callback(null, result);
            } else {
                return callback(error, null);
            }
        });
    });
};

//------------------------------------------------------------------
/**
 * 根据秒杀订单 id 获取商品的基本信息
 * @param skOrderId 秒杀订单唯一标识
 * */
ItbTradeOrderModel.prototype.getSeckillOrderItemInfo = function (skOrderId, callback) {
    pool.pool.getConnection(function(err, connection) {
        if (err) {
            console.log('ItbTradeOrderModel.getSeckillOrderItemInfo Err:'+err);
            return callback(err,null);
        }
        var sql =
            ' SELECT o.*, '+
            '        d.fee AS detail_fee, '+
            '        d.product_fee AS detail_prod_fee, '+
            '        d.design_fee AS detail_design_fee, '+
            '        d.mail_fee AS detail_mail_fee, '+
            '        d.detail_id, d.detail_no, '+
            '        ar.act_id, ar.r_id, '+
            '        ar.item_id, ar.pp_id, ar.item_qty, ar.item_unit, '+
            '        it.item_name, it.design_id '+
            ' FROM itb_trade_order o, '+
            '      itb_trade_order_detail d, '+// 只能有一个子订单
            '      itb_activity_r ar, '+
            '      itb_items it '+
            ' WHERE o.del_flg=0 AND o.type=20 AND o.order_id='+skOrderId+
            ' AND d.del_flg=0 AND d.order_id=o.order_id AND d.detail_no="0" '+
            ' AND ar.del_flg=0 AND ar.r_id=d.item_id '+
            ' AND it.del_flg=0 AND it.item_id=ar.item_id '+
            ' ORDER BY d.detail_id ';

        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();

            if(result && result.length>0) {
                return callback(null, result[0]);
            } else {
                return callback(error, null);
            }
        });
    });
};

/**
 * 根据秒杀订单 id 获取秒杀订单所需填写资料信息
 *
 * @param actOrderId 秒杀订单唯一标识
 * @param callback 回调
 *          err: 错误
 *          row : {
 *              doc_id: 1,
 *              coreStruct : {},
 *              ver : 3,
 *              state : 11,
 *              typeM : 40100,
 *              typeL : 4
 *          }
 *
 * */
ItbTradeOrderModel.prototype.getActivityDocInfoByActOrderId = function (actOrderId, callback) {
    pool.pool.getConnection(function(err, connection) {
        if (err) {
            console.log('ItbTradeOrderModel.getActivityDocInfoByActOrderId Err:'+err);
            return callback(err,null);
        }
        var sql =
            " SELECT ud.doc_id, " +
            "        ud.data AS coreStructure, " +
            "        ud.ver, " +
            "        if(NOW() > ar.expiry_time, 1, 0) AS expiry, " +
            "        o.state, " +
            "        o.order_id, " +
            "        t.parent AS typeM, " +
            "        t.top_parent AS typeL " +
            " FROM itb_trade_order o, " +
            "      itb_trade_order_detail od, " +
            "      tg_user_doc ud, " +
            "      itb_activity_r ar, " +
            "      tg_type t " +
            " WHERE o.del_flg=0 " +
            "   AND o.type=0 " +
            "   AND o.pay_order_no = " +
            "     (SELECT o.pay_order_no " +
            "      FROM itb_trade_order o " +
            "      WHERE o.order_id=" + actOrderId +
            "        AND o.type=20 " +
            "        AND o.del_flg=0) " +
            "   AND od.order_id=o.order_id " +
            "   AND od.user_doc_id IS NOT NULL " +
            "   AND od.del_flg=0 " +
            "   AND ud.doc_id=od.user_doc_id " +
            "   AND ud.del_flg=0 " +
            "   AND ar.r_id in " +
            "     (SELECT d.item_id " +
            "      FROM itb_trade_order_detail d " +
            "      WHERE d.order_id="+ actOrderId +
            "        AND d.del_flg=0) " +
            "   AND t.type_id = ud.type_id ";
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();

            if(result && result.length>0) {
                return callback(null, result[0]);
            } else {
                return callback(error, null);
            }
        });
    });
};

/**
 * 确认订单是否是秒杀订单
 *
 * @param orderId 普通订单唯一标识
 * @param callback 回调
 *             err: 错误
 *             isSeckill false 非秒杀订单 true, 秒杀订单
 * */

ItbTradeOrderModel.prototype.chkOrderIsSeckillActivityOrder = function(orderId, callback) {
    pool.pool.getConnection(function(err, connection) {
        if (err) {
            console.log('ItbTradeOrderModel.getActivityDocInfoByActOrderId Err:'+err);
            return callback(err,null);
        }
        var sql =
            " SELECT * " +
            " FROM itb_trade_order o " +
            " WHERE o.type=20 " +
            "   AND o.del_flg=0 " +
            "   AND o.pay_order_no = " +
            "     (SELECT o.pay_order_no " +
            "      FROM itb_trade_order o " +
            "      WHERE o.order_id=" + orderId +
            "        AND o.del_flg=0) ";
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();

            if(result && result.length>0) {
                return callback(null, true);
            } else {
                return callback(error, false);
            }
        });
    });
};


/**
 * 获取秒杀的 logo 订单源文件
 *
 * */

ItbTradeOrderModel.prototype.getSeckillActivityOrderSourceUrl = function(orderId, callback) {
    pool.pool.getConnection(function(err, connection) {
        if (err) {
            console.log('ItbTradeOrderModel.getActivityDocInfoByActOrderId Err:'+err);
            return callback(err,null);
        }
        var sql = " SELECT dd.* " +
            " FROM itb_trade_order_detail od, " +
            "      tg_user_doc ud, " +
            "      tg_design_doc dd " +
            " WHERE od.order_id=" + orderId +
            "   AND od.del_flg=0 " +
            "   AND od.user_doc_id IS NOT NULL " +
            "   AND ud.doc_id=od.user_doc_id " +
            "   AND ud.del_flg=0 " +
            "   AND dd.design_id = ud.design_id " +
            "   AND dd.del_flg=0 ";
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();
            if(result && result.length>0) {
                return callback(null, result[0]);
            } else {
                return callback(error, null);
            }
        });
    });
};


//------------------------------------------------------------------
/* 一键生成： 最近一个月    印品    已完成   订单    的  id
 * 参数：callback：回调，参数如下：
 *          error:错误消息
 *          data: 订单id列表
 */
ItbTradeOrderModel.prototype.getLastMonthFinishOrderIds= function( callback){
    var THIS = this;
    var tableName = this.getTableName();

    pool.pool.getConnection(function(err, connection) {
        if (err) {
            console.log('ItbTradeOrderModel.getLastMonthFinishOrderIds Err:'+err);
            return callback(err, null);
        }
        // Use the connection
        var sql =
            "	SELECT DISTINCT t.order_id, tt.user_doc_id,t.order_no"+
            "	FROM itbour.itb_trade_order t"+
            "	INNER JOIN itbour.itb_trade_order_detail tt"+
            "	ON tt.order_id = t.order_id"+
            "	AND tt.del_flg = 0"+
            "	WHERE t.del_flg = 0"+
            "	AND t.state = 40"+
            "	AND t.type = 0"+
            "	AND DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= DATE(t.create_time)"+
            "	AND tt.user_doc_id is not null"+
            "	group by (order_id)	";

        //var sqlValue = [];
        //var sql = mysql.format(sql, sqlValue);
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();

            console.log();

            if(error){
                return callback(error, null);
            }
            if(!result || result.length==0){
                return callback(null,null);
            }
            var ret = [];
            for(var i=0;i<result.length;i++){
                ret.push(result[i].order_id);
            }

            return callback(null, ret);
        });
    });
}


// 导出
module.exports = ItbTradeOrderModel;